Skip Headers

Oracle® Database Data Cartridge Developer's Guide, 10g Release 2 (10.2) Part Number B14289-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 16 PSBTREE: Extensible Indexing Example

This chapter presents an extensible indexing example in which some of the `ODCIIndex` interface routines are implemented in C.

This chapter contains these topics:

- Introduction to the PSBTREE Example

- Design of the Indextype

- Implementing Operators

- Implementing the Index Routines

- The C Code

- Implementing the Indextype

- Usage Examples

## Introduction to the PSBTREE Example

The example in this chapter gives a general illustration of how to implement the extensible indexing interface routines in C. The example tries to concentrate on topics that are common to all implementations and glosses over domain-specific details.

The code for the example is in the demo directory (see file `extdemo5.sql`). It extends an earlier example (`extdemo2.sql`, also in demo directory) by adding to the indextype support for local domain indexes on range partitioned tables.

## Design of the Indextype

The indextype implemented here, called `PSBtree,` operates like a btree index. It supports three user-defined operators:

- `gt(Greater Than)`

- `lt(Less Than)`

- `eq(EQuals)`

These operators operate on operands of `VARCHAR2` datatype.

The index data consists of records of the form `<key, rid>` where `key` is the value of the indexed column and `rid` is the row identifier of the corresponding row. To simplify the implementation of the indextype, the index data is stored in an index-organized table.

When an index is a local domain index, one index-organized table is created for each partition to store the index data for that partition. Thus, the index manipulation routines merely translate operations on the `PSBtree` into operations on the table storing the index data.

When a user creates a `PSBtree` index (a local index), `n` tables are created consisting of the indexed column and a `rowid` column, where `n` is the number of partitions in the base table. Inserts into the base table cause appropriate insertions into the affected index table. Deletes and updates are handled similarly. When the `PSBtree` is queried based on a user-defined operator (one of `gt`, `lt` and `eq`), an appropriate query is issued against the index table to retrieve all the satisfying rows. Appropriate partition pruning occurs, and only the index tables that correspond to the relevant, or "interesting", partitions are accessed.

## Implementing Operators

The `PSBtree` indextype supports three operators. Each operator has a corresponding functional implementation. The functional implementations of the `eq`, `gt` and `lt` operators are presented in the following section.

### Create Functional Implementations

The following sections describe functional implementation of comparison operators.

#### Functional Implementation of EQ (EQUALS)

The functional implementation for `eq` is provided by a function (`bt_eq`) that takes in two `VARCHAR2` parameters and returns `1` if they are equal and `0` otherwise.

    CREATE FUNCTION bt_eq(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
    BEGIN
      IF a = b then
        RETURN 1;
      ELSE
        RETURN 0;
      END IF;
    END;

#### Functional Implementation of LT (LESS THAN)

The functional implementation for `lt` is provided by a function (`bt_lt`) that takes in two `VARCHAR2` parameters and returns `1` if the first parameter is less than the second, `0` otherwise.

    CREATE FUNCTION bt_lt(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
    BEGIN
      IF a < b then
        RETURN 1;
      ELSE
       RETURN 0;
      END IF;
    END;

#### Functional Implementation of GT (GREATER THAN)

The functional implementation for `gt` is provided by a function (`bt_gt`) that takes in two `VARCHAR2` parameters and returns `1` if the first parameter is greater than the second, `0` otherwise.

    CREATE FUNCTION bt_gt(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
    BEGIN
      IF a > b then
        RETURN 1;
      ELSE
        RETURN 0;
      END IF;
    END;

### Create Operators

To create the operator, you need to specify the signature of the operator along with its return type and its functional implementation.

#### Operator EQ

    CREATE OPERATOR eq
    BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER
    USING bt_eq;

#### Operator LT

    CREATE OPERATOR lt
    BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER
    USING bt_lt;

#### Operator GT

    CREATE OPERATOR gt
    BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER
    USING bt_gt;

## Implementing the Index Routines

1. Define an implementation type that implements the `ODCIIndex` interface routines.

       CREATE TYPE psbtree_im AS OBJECT
       (
         scanctx RAW(4),
         STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList)
           RETURN NUMBER,
         STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2,
           env SYS.ODCIEnv) RETURN NUMBER,
                 STATIC FUNCTION ODCIIndexDrop(ia SYS.ODCIIndexInfo, env SYS.ODCIEnv)
           RETURN NUMBER,
         STATIC FUNCTION ODCIIndexExchangePartition(ia SYS.ODCIIndexInfo,
           ia1 SYS.ODCIIndexInfo, env SYS.ODCIEnv) RETURN NUMBER,
         STATIC FUNCTION ODCIIndexMergePartition(ia SYS.ODCIIndexInfo,
           part_name1 SYS.ODCIPartInfo, part_name2 SYS.ODCIPartInfo, parms VARCHAR2,
           env SYS.ODCIEnv) RETURN NUMBER,
         STATIC FUNCTION ODCIIndexSplitPartition(ia SYS.ODCIIndexInfo,
           part_name1 SYS.ODCIPartInfo, part_name2 SYS.ODCIPartInfo, parms VARCHAR2,
           env SYS.ODCIEnv) RETURN NUMBER,
         STATIC FUNCTION ODCIIndexTruncate(ia SYS.ODCIIndexInfo, env SYS.ODCIEnv)
           RETURN NUMBER,
         STATIC FUNCTION ODCIIndexInsert(ia SYS.ODCIIndexInfo, rid VARCHAR2,
           newval VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
         STATIC FUNCTION ODCIIndexDelete(ia SYS.ODCIIndexInfo, rid VARCHAR2,
           oldval VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
         STATIC FUNCTION ODCIIndexUpdate(ia SYS.ODCIIndexInfo, rid VARCHAR2,
           oldval VARCHAR2, newval VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
         STATIC FUNCTION ODCIIndexStart(sctx IN OUT psbtree_im, ia SYS.ODCIIndexInfo,
           op SYS.ODCIPredInfo, qi sys.ODCIQueryInfo, strt number, stop number,
           cmpval VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
         MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT SYS.ODCIridlist,
           env SYS.ODCIEnv) RETURN NUMBER,
         MEMBER FUNCTION ODCIIndexClose(env SYS.ODCIEnv) RETURN NUMBER
       );
       /
       SHOW ERRORS

2. Define the implementation type body

   You can implement the index routines in any language supported by Oracle. For this example, we will implement the get interfaces routine and the index definition routines in PL/SQL. We will implement the index manipulation and query routines in C.

       CREATE OR REPLACE TYPE BODY psbtree_im
       IS

The get interfaces routine returns the expected interface name through its `OUT` parameter.

    STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
           RETURN NUMBER IS
       BEGIN
           ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2'));
           RETURN ODCIConst.Success;
       END ODCIGetInterfaces;

The `ODCIIndexCreate` routine creates an index storage table with two columns. The first column stores the `VARCHAR2` indexed column value. The second column in the index table stores the `rowid` of the corresponding row in the base table. When the create routine is invoked during creation of a local domain index, it is invoked `n`+2 times, where `n` is the number of partitions in the base table. The routine creates one index storage table for each partition. The create routine is also invoked during execution of `ALTER TABLE ADD PARTITION` if there are local domain indexes defined on the table. In this case, the routine simply creates a new index storage table to correspond to the newly created partition. The routine makes use of the information passed in to determine the context in which it is invoked. `DBMS_SQL` is used to execute the dynamically constructed SQL statement.

    STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2,
        env SYS.ODCIEnv)
        RETURN NUMBER
      IS
       i INTEGER;
       stmt VARCHAR2(1000);
       cnum INTEGER;
       junk INTEGER;
      BEGIN
       -- construct the sql statement
       stmt := '';

       IF ((env.CallProperty IS NULL) and (ia.IndexPartition IS NULL ))  THEN
         stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
                 '_sbtree'  ||
                 '( f1 , f2, PRIMARY KEY (f1) ) ORGANIZATION INDEX AS SELECT ' ||
                 ia.IndexCols(1).ColName || ', ROWID FROM ' ||
                 ia.IndexCols(1).TableSchema || '.' || ia.IndexCols(1).TableName;
       END IF;

       IF ((env.CallProperty IS NOT NULL) AND (ia.IndexPartition IS NOT NULL)) THEN
         stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
                 '_' || ia.indexpartition || '_sbtree'  ||
                 '( f1 , f2, PRIMARY KEY (f1) ) ORGANIZATION INDEX AS SELECT ' ||
                 ia.IndexCols(1).ColName || ', ROWID FROM ' ||
                 ia.IndexCols(1).TableSchema || '.' ||
                 ia.IndexCols(1).TableName || ' PARTITION (' ||
                 ia.IndexCols(1).TablePartition || ')';
       END IF;

       IF ((env.CallProperty IS NULL) AND (ia.IndexPartition IS NOT NULL)) THEN
         stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
                 '_' || ia.IndexPartition || '_sbtree'  ||
                 '( f1 ' || ia.IndexCols(1).ColTypeName ||'(200) , f2 ROWID, ' ||
                 ' PRIMARY KEY (f1)) ORGANIZATION INDEX';
       END IF;

       DBMS_OUTPUT.PUT_LINE('Create');
       DBMS_OUTPUT.PUT_LINE(stmt);

       -- execute the statement
       IF ( (env.CallProperty IS NULL) OR
            (env.CallProperty = SYS.ODCIConst.IntermediateCall) ) THEN
         cnum := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
         junk := DBMS_SQL.EXECUTE(cnum);
         DBMS_SQL.CLOSE_CURSOR(cnum);
       END IF;

       RETURN ODCIConst.Success;
      END;

The `ODCIIndexDrop` routine drops the index storage table(s). For a local domain index, the routine is invoked `n`+2 times, where `n` is the number of partitions in the base table.

    STATIC FUNCTION ODCIIndexDrop(ia SYS.ODCIIndexInfo, env SYS.ODCIEnv)
       RETURN NUMBER IS
       stmt VARCHAR2(1000);
       cnum INTEGER;
       junk INTEGER;
      BEGIN
        -- construct the sql statement
       stmt := '';
       IF ((env.CallProperty IS NULL) and (ia.IndexPartition IS NULL) ) THEN
         stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
                 '_sbtree';
       ELSE
         IF (ia.IndexPartition IS NOT NULL) THEN
           stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
                   '_' || ia.IndexPartition || '_sbtree';
         END IF;
       END IF;

       DBMS_OUTPUT.PUT_LINE('Drop');
       DBMS_OUTPUT.PUT_LINE(stmt);

       -- execute the statement
       IF ( (env.CallProperty IS NULL) OR
            (env.CallProperty = SYS.ODCIConst.IntermediateCall) ) THEN
         cnum := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
         junk := DBMS_SQL.EXECUTE(cnum);
         DBMS_SQL.CLOSE_CURSOR(cnum);
       END IF;

       RETURN ODCIConst.Success;
      END;

To handle partition maintenance operations, the indextype also has additional methods that take appropriate actions on the index storage tables when the base table partitions are merged, split, or exchanged.

The `ODCIIndexMergePartition` routine drops the index storage tables for the two index partitions being merged and creates a new table corresponding to the resulting merged partition. If there is data in the resulting merged partition, the index is marked `UNUSABLE` so that the table is not populated with rows. That is left to be done during a subsequent `ALTER INDEX REBUILD PARTITION`.

    STATIC FUNCTION ODCIIndexMergePartition(ia SYS.ODCIIndexInfo,
        part_name1 SYS.ODCIPartInfo, part_name2 SYS.ODCIPartInfo,
        parms VARCHAR2, env SYS.ODCIEnv)
        return number
      IS
       stmt VARCHAR2(2000);
       cnum INTEGER;
       junk INTEGER;
      BEGIN
       DBMS_OUTPUT.PUT_LINE('Merge Partitions');
       stmt := '';

       IF (ia.IndexPartition IS NOT NULL) THEN
          stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
                   '_' || ia.IndexPartition || '_sbtree';

          DBMS_OUTPUT.PUT_LINE('drop');
          DBMS_OUTPUT.PUT_LINE(stmt);

          -- execute the statement
          cnum := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
          junk := DBMS_SQL.EXECUTE(cnum);
          DBMS_SQL.CLOSE_CURSOR(cnum);
       END IF;

       IF ( part_name1 IS NOT NULL) THEN
          stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
                   '_' || part_name1.IndexPartition || '_sbtree';

          DBMS_OUTPUT.PUT_LINE('drop');
          DBMS_OUTPUT.PUT_LINE(stmt);

          -- execute the statement
          cnum := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
          junk := DBMS_SQL.EXECUTE(cnum);
          DBMS_SQL.CLOSE_CURSOR(cnum);
       END IF;

       IF ( part_name2 IS NOT NULL) THEN
          stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
               '_' || part_name2.IndexPartition ||  '_sbtree'  ||
               '( f1 ' || ia.IndexCols(1).ColTypeName ||
               '(200) , f2 ROWID, PRIMARY KEY (f1) ) ORGANIZATION INDEX';

          DBMS_OUTPUT.PUT_LINE('create');
          DBMS_OUTPUT.PUT_LINE('Parameter string : ' || parms);

          DBMS_OUTPUT.PUT_LINE(stmt);

          -- execute the statement
          cnum := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
          junk := DBMS_SQL.EXECUTE(cnum);
          DBMS_SQL.CLOSE_CURSOR(cnum);
        END IF;

        RETURN ODCIConst.Success;
      END;

The `ODCIIndexSplitPartition` routine drops the index storage table corresponding to the partition being split and creates two new index tables that correspond to the two new index partitions created.

    STATIC FUNCTION ODCIIndexSplitPartition(ia SYS.ODCIIndexInfo,
        part_name1 SYS.ODCIPartInfo, part_name2 SYS.ODCIPartInfo,
        parms VARCHAR2, env sys.odcienv)
        return number
      IS
       stmt VARCHAR2(2000);
       cnum INTEGER;
       junk INTEGER;
      BEGIN
       DBMS_OUTPUT.PUT_LINE('Split Partition');
       stmt := '';

       IF (ia.IndexPartition IS NOT NULL) THEN
          stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
                   '_' || ia.IndexPartition || '_sbtree';

          DBMS_OUTPUT.PUT_LINE('drop');
          DBMS_OUTPUT.PUT_LINE(stmt);

          -- execute the statement
          cnum := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
          junk := DBMS_SQL.EXECUTE(cnum);
          DBMS_SQL.CLOSE_CURSOR(cnum);
        END IF;

       IF ( part_name1 IS NOT NULL) THEN
          stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
               '_' || part_name1.IndexPartition ||  '_sbtree'  ||
               '( f1 ' || ia.IndexCols(1).ColTypeName ||
               '(200) , f2 ROWID, PRIMARY KEY (f1)) ORGANIZATION INDEX';

          DBMS_OUTPUT.PUT_LINE('create');
          DBMS_OUTPUT.PUT_LINE('Parameter string : ' || parms);
          DBMS_OUTPUT.PUT_LINE(stmt);

          -- execute the statement
          cnum := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
          junk := DBMS_SQL.EXECUTE(cnum);
          DBMS_SQL.CLOSE_CURSOR(cnum);
        END IF;

       IF ( part_name2 IS NOT NULL) THEN
          stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
               '_' || part_name2.IndexPartition ||  '_sbtree'  ||
               '( f1 ' || ia.IndexCols(1).ColTypeName ||
               '(200) , f2 ROWID, PRIMARY KEY (f1)) ORGANIZATION INDEX';

          DBMS_OUTPUT.PUT_LINE('create');
          DBMS_OUTPUT.PUT_LINE('Parameter string : ' || parms);
          DBMS_OUTPUT.PUT_LINE(stmt);

          -- execute the statement
          cnum := DBMS_SQL.OPEN_CURSOR;
          DBMS_SQL.PARSE(cnum, stmt, dbms_sql.native);
          junk := DBMS_SQL.EXECUTE(cnum);
          DBMS_SQL.CLOSE_CURSOR(cnum);
        END IF;

        RETURN ODCIConst.Success;
      END;

The `ODCIIndexExchangePartition` exchanges the index storage tables for the index partition being exchanged, with the index storage table for the global domain index.

    STATIC FUNCTION ODCIIndexExchangePartition(ia SYS.ODCIIndexInfo,
        ia1 SYS.ODCIIndexInfo, env SYS.ODCIEnv)
       RETURN NUMBER
      IS
       stmt VARCHAR2(2000);
       cnum INTEGER;
       junk INTEGER;
      BEGIN
       stmt := '';
       DBMS_OUTPUT.PUT_LINE('Exchange Partitions');

        -- construct the sql statement
       stmt := 'ALTER TABLE temp EXCHANGE PARTITION p1 WITH TABLE ' ||
                    ia1.IndexSchema || '.' || ia1.IndexName || '_sbtree';
       cnum := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
       junk := DBMS_SQL.EXECUTE(cnum);
       DBMS_SQL.CLOSE_CURSOR(cnum);

       stmt := 'ALTER TABLE temp EXCHANGE PARTITION p1 WITH TABLE ' ||
            ia.IndexSchema || '.' || ia.IndexName ||
            '_' || ia.IndexPartition || '_sbtree';

       cnum := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
       junk := DBMS_SQL.EXECUTE(cnum);
       DBMS_SQL.CLOSE_CURSOR(cnum);

       stmt := 'ALTER TABLE temp EXCHANGE PARTITION p1 WITH TABLE ' ||
            ia1.IndexSchema || '.' || ia1.IndexName || '_sbtree';
       cnum := DBMS_SQL.OPEN_CURSOR;
       DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
       junk := DBMS_SQL.EXECUTE(cnum);
       DBMS_SQL.CLOSE_CURSOR(cnum);

       RETURN ODCIConst.Success;
      END;

The index manipulation and query routines are implemented in C. These require some setup to be done in advance. Specifically, you need to create a library object called `extdemo5l` for your compiled C code.

After the setup, the following statements register the implementation of the index manipulation and query routines in terms of their corresponding C functions.

Register the implementation of the `ODCIIndexInsert` routine.

    STATIC FUNCTION ODCIIndexInsert(ia SYS.ODCIIndexInfo, rid VARCHAR2,
                                  newval VARCHAR2, env SYS.ODCIEnv)
                                  RETURN NUMBER AS EXTERNAL
    name "qxiqtbi"
    library extdemo5l
    with context
    parameters (
    context,
    ia,
    ia indicator struct,
    rid,
    rid indicator,
    newval,
    newval indicator,
    env,
    env indicator struct,
    return OCINumber
               );

Register the implementation of the `ODCIIndexDelete` routine.

    STATIC FUNCTION ODCIIndexDelete(ia SYS.ODCIIndexInfo, rid VARCHAR2,
                                    oldval VARCHAR2, env SYS.ODCIEnv)
                                    RETURN NUMBER AS EXTERNAL
    name "qxiqtbd"
    library extdemo5l
    with context
    parameters (
    context,
    ia,
    ia indicator struct,
    rid,
    rid indicator,
    oldval,
    oldval indicator,
    env,
    env indicator struct,
    return OCINumber
               );

Register the implementation of the `ODCIIndexUpdate` routine.

    STATIC FUNCTION ODCIIndexUpdate(ia SYS.ODCIIndexInfo, rid VARCHAR2,
                              oldval VARCHAR2, newval VARCHAR2, env SYS.ODCIEnv)
                                    RETURN NUMBER AS EXTERNAL
    name "qxiqtbu"
    library extdemo5l
    with context
    parameters (
    context,
    ia,
    ia indicator struct,
    rid,
    rid indicator,
    oldval,
    oldval indicator,
    newval,
    newval indicator,
    env,
    env indicator struct,
    return OCINumber
               );

Register the implementation of the `ODCIIndexStart` routine.

    STATIC FUNCTION ODCIIndexStart(sctx IN OUT psbtree_im, ia SYS.ODCIIndexInfo,
                             op SYS.ODCIPredInfo,
                             qi SYS.ODCIQueryInfo,
                             strt NUMBER,
                             stop NUMBER,
                             cmpval VARCHAR2,
                             env SYS.ODCIEnv)
       RETURN NUMBER AS EXTERNAL
       name "qxiqtbs"
       library extdemo5l
       with context
       parameters (
         context,
         sctx,
         sctx indicator struct,
         ia,
         ia indicator struct,
         op,
         op indicator struct,
         qi,
         qi indicator struct,
         strt,
         strt indicator,
         stop,
         stop indicator,
         cmpval,
         cmpval indicator,
         env,
         env indicator struct,
         return OCINumber
      );

Register the implementation of the `ODCIIndexFetch` routine.

    MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT SYS.ODCIRidList,
         env SYS.ODCIEnv)
     RETURN NUMBER AS EXTERNAL
     name "qxiqtbf"
     library extdemo5l
     with context
     parameters (
       context,
       self,
       self indicator struct,
       nrows,
       nrows indicator,
       rids,
       rids indicator,
       env,
       env indicator struct,
       return OCINumber
     );

Register the implementation of the `ODCIIndexClose` routine.

    MEMBER FUNCTION ODCIIndexClose (env SYS.ODCIEnv) RETURN NUMBER AS EXTERNAL
    name "qxiqtbc"
    library extdemo5l
    with context
    parameters (
      context,
      self,
      self indicator struct,
      env,
      env indicator struct,
      return OCINumber
    );

## The C Code

The C structs for mapping the ODCI types are defined in the file `odci.h`. For example, the C struct `ODCIIndexInfo` is the mapping for the corresponding ODCI object type. The C struct `ODCIIndexInfo_ind` is the mapping for the null object.

### Common Error Processing Routine

This function is used to check and process the return code from all `OCI` routines. It checks the status code and raises an exception in case of errors.

    static int qxiqtce(ctx, errhp, status)
    OCIExtProcContext *ctx;
    OCIError *errhp;
    sword status;
    {
      text errbuf[512];
      sb4 errcode = 0;
      int errnum = 29400;  /* choose some oracle error number */
      int rc = 0;

      switch (status)
      {
      case OCI_SUCCESS:
        rc = 0;
        break;
      case OCI_ERROR:
        (void) OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode,
                           errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
        /* Raise exception */
        OCIExtProcRaiseExcpWithMsg(ctx, errnum, errbuf, strlen((char *)errbuf));
        rc = 1;
        break;
      default:
        (void) sprintf((char *)errbuf, "Warning - some error\n");
        /* Raise exception */
        OCIExtProcRaiseExcpWithMsg(ctx, errnum, errbuf, strlen((char *)errbuf));
        rc = 1;
        break;
      }
      return (rc);
    }

### Implementation Of The ODCIIndexInsert Routine

The insert routine parses and executes a statement that inserts a new row into the index table. The new row consists of the new value of the indexed column and the `rowid` that have been passed in as parameters.

    OCINumber *qxiqtbi(ctx, ix, ix_ind, rid, rid_ind,
                       newval, newval_ind, env, env_ind)
    OCIExtProcContext *ctx;
    ODCIIndexInfo     *ix;
    ODCIIndexInfo_ind *ix_ind;
    char              *rid;
    short             rid_ind;
    char              *newval;
    short             newval_ind;
    ODCIEnv           *env;
    ODCIEnv_ind       *env_ind;
    {
      OCIEnv *envhp = (OCIEnv *) 0;                /* env. handle */
      OCISvcCtx *svchp = (OCISvcCtx *) 0;          /* service handle */
      OCIError *errhp = (OCIError *) 0;            /* error handle */
      OCIStmt *stmthp = (OCIStmt *) 0;             /* statement handle */
      OCIBind *bndp = (OCIBind *) 0;               /* bind handle */
      OCIBind *bndp1 = (OCIBind *) 0;              /* bind handle */

      int retval = (int)ODCI_SUCCESS;              /* return from this function */
      OCINumber *rval = (OCINumber *)0;
      ub4 key;                                     /* key value set in "self" */

      char insstmt[2000];                          /* sql insert statement */

      /* allocate memory for OCINumber first */
      rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));

      /* Get oci handles */
      if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
        return(rval);

      /* set up return code */
      if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                               sizeof(retval),
                                               OCI_NUMBER_SIGNED, rval)))
        return(rval);

      /******************************
       * Construct insert Statement *
       ******************************/
       if (ix_ind->IndexPartition == OCI_IND_NULL)
       {
         sprintf(insstmt,
                      "INSERT into %s.%s_sbtree values (:newval, :mrid)",
                      OCIStringPtr(envhp, ix->IndexSchema),
                      OCIStringPtr(envhp, ix->IndexName));
       }
       else
       {
         sprintf(insstmt,
                      "INSERT into %s.%s_%s_sbtree values (:newval, :mrid)",
                      OCIStringPtr(envhp, ix->IndexSchema),
                      OCIStringPtr(envhp, ix->IndexName),
                      OCIStringPtr(envhp, ix->IndexPartition));

          }

      /****************************************
       * Parse and Execute Insert Statement   *
       ****************************************/

      /* allocate stmt handle */
      if (qxiqtce(ctx, errhp, OCIHandleAlloc((dvoid *)envhp,
                                             (dvoid **)&stmthp,
                                             (ub4)OCI_HTYPE_STMT, (size_t)0,
                                             (dvoid **)0)))
        return(rval);

      /* prepare the statement */
      if (qxiqtce(ctx, errhp, OCIStmtPrepare(stmthp, errhp, (text *)insstmt,
                                             (ub4)strlen(insstmt), OCI_NTV_SYNTAX,
                                             OCI_DEFAULT)))
        return(rval);

      /* Set up bind for newval */
      if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)1,
                                           (dvoid *)newval,
                                           (sb4)(strlen(newval)+1),
                                           (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                           (ub2 *)0, (ub4)0, (ub4 *)0,
                                           (ub4)OCI_DEFAULT)))
        return(rval);

      /* Set up bind for rid */
      if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)2,
                                           (dvoid *)rid,
                                           (sb4)(strlen(rid)+1),
                                           (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                           (ub2 *)0, (ub4)0, (ub4 *)0,
                                           (ub4)OCI_DEFAULT)))
        return(rval);

      /* Execute statement */
      if (qxiqtce(ctx, errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1,
                                             (ub4)0, (OCISnapshot *)NULL,
                                             (OCISnapshot *)NULL,
                                             (ub4)OCI_DEFAULT)))
        return(rval);

      return(rval);
    }

### Implementation of the ODCIIndexDelete Routine

The delete routine constructs a SQL statement to delete a row from the index table corresponding to the row being deleted from the base table. The row in the index table is identified by the value of `rowid` that is passed in as a parameter to this routine.

    OCINumber *qxiqtbd(ctx, ix, ix_ind, rid, rid_ind,
                       oldval, oldval_ind, env, env_ind)
    OCIExtProcContext *ctx;
    ODCIIndexInfo     *ix;
    ODCIIndexInfo_ind *ix_ind;
    char              *rid;
    short             rid_ind;
    char              *oldval;
    short             oldval_ind;
    ODCIEnv           *env;
    ODCIEnv_ind       *env_ind;
    {
      OCIEnv *envhp = (OCIEnv *) 0;                /* env. handle */
      OCISvcCtx *svchp = (OCISvcCtx *) 0;          /* service handle */
      OCIError *errhp = (OCIError *) 0;            /* error handle */
      OCIStmt *stmthp = (OCIStmt *) 0;             /* statement handle */
      OCIBind *bndp = (OCIBind *) 0;               /* bind handle */
      OCIBind *bndp1 = (OCIBind *) 0;              /* bind handle */

      int retval = (int)ODCI_SUCCESS;              /* return from this function */
      OCINumber *rval = (OCINumber *)0;
      ub4 key;                                     /* key value set in "self" */

      char delstmt[2000];                          /* sql insert statement */

      /* Get oci handles */
      if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
        return(rval);

      /* set up return code */
      rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
      if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                               sizeof(retval),
                                               OCI_NUMBER_SIGNED, rval)))
        return(rval);

      /******************************
       * Construct delete Statement *
       ******************************/
       if (ix_ind->IndexPartition == OCI_IND_NULL)
       {
         sprintf(delstmt,
                      "DELETE FROM %s.%s_sbtree WHERE f2 = :rr",
                      OCIStringPtr(envhp, ix->IndexSchema),
                      OCIStringPtr(envhp, ix->IndexName));
       }
       else
       {
         sprintf(delstmt,
                      "DELETE FROM %s.%s_%s_sbtree WHERE f2 = :rr",
                      OCIStringPtr(envhp, ix->IndexSchema),
                      OCIStringPtr(envhp, ix->IndexName),
                      OCIStringPtr(envhp, ix->IndexPartition));

       }

      /****************************************
       * Parse and Execute delete Statement   *
       ****************************************/

      /* allocate stmt handle */
      if (qxiqtce(ctx, errhp, OCIHandleAlloc((dvoid *)envhp,
                                             (dvoid **)&stmthp,
                                             (ub4)OCI_HTYPE_STMT, (size_t)0,
                                             (dvoid **)0)))
        return(rval);

      /* prepare the statement */
      if (qxiqtce(ctx, errhp, OCIStmtPrepare(stmthp, errhp, (text *)delstmt,
                                             (ub4)strlen(delstmt), OCI_NTV_SYNTAX,
                                             OCI_DEFAULT)))
        return(rval);

      /* Set up bind for rid */
      if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)1,
                                           (dvoid *)rid,
                                           (sb4)(strlen(rid)+1),
                                           (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                           (ub2 *)0, (ub4)0, (ub4 *)0,
                                           (ub4)OCI_DEFAULT)))
        return(rval);

      /* Execute statement */
      if (qxiqtce(ctx, errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1,
                                             (ub4)0, (OCISnapshot *)NULL,
                                             (OCISnapshot *)NULL,
                                             (ub4)OCI_DEFAULT)))
        return(rval);

      return(rval);
    }

### Implementation of the ODCIIndexUpdate Routine

The update routine constructs a SQL statement to update a row in the index table corresponding to the row being updated in the base table. The row in the index table is identified by the value of `rowid` that is passed in as a parameter to this routine. The old column value (`oldval`) is replaced by the new value (`newval`).

    OCINumber *qxiqtbu(ctx, ix, ix_ind, rid, rid_ind,
                       oldval, oldval_ind, newval, newval_ind, env, env_ind)
    OCIExtProcContext *ctx;
    ODCIIndexInfo     *ix;
    ODCIIndexInfo_ind *ix_ind;
    char              *rid;
    short             rid_ind;
    char              *oldval;
    short             oldval_ind;
    char              *newval;
    short             newval_ind;
    ODCIEnv           *env;
    ODCIEnv_ind       *env_ind;
    {
      OCIEnv *envhp = (OCIEnv *) 0;                /* env. handle */
      OCISvcCtx *svchp = (OCISvcCtx *) 0;          /* service handle */
      OCIError *errhp = (OCIError *) 0;            /* error handle */
      OCIStmt *stmthp = (OCIStmt *) 0;             /* statement handle */
      OCIBind *bndp = (OCIBind *) 0;               /* bind handle */
      OCIBind *bndp1 = (OCIBind *) 0;              /* bind handle */

      int retval = (int)ODCI_SUCCESS;              /* return from this function */
      OCINumber *rval = (OCINumber *)0;
      ub4 key;                                     /* key value set in "self" */

      char updstmt[2000];                          /* sql insert statement */

      /* Get oci handles */
      if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
        return(rval);

      /* set up return code */
      rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
      if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                               sizeof(retval),
                                               OCI_NUMBER_SIGNED, rval)))
        return(rval);

      /******************************
       * Construct update Statement *
       ******************************/
       if (ix_ind->IndexPartition == OCI_IND_NULL)
       {
         sprintf(updstmt,
              "UPDATE %s.%s_sbtree SET f1 = :newval, f2 = :rr WHERE f1 = :oldval",
                      OCIStringPtr(envhp, ix->IndexSchema),
                      OCIStringPtr(envhp, ix->IndexName));
       }
       else
       {
         sprintf(updstmt,
           "UPDATE %s.%s_%s_sbtree SET f1 = :newval, f2 = :rr WHERE f1 = :oldval",
                      OCIStringPtr(envhp, ix->IndexSchema),
                      OCIStringPtr(envhp, ix->IndexName),
                      OCIStringPtr(envhp, ix->IndexPartition));

       }

      /****************************************
       * Parse and Execute Update Statement   *
       ****************************************/

      /* allocate stmt handle */
      if (qxiqtce(ctx, errhp, OCIHandleAlloc((dvoid *)envhp,
                                             (dvoid **)&stmthp,
                                             (ub4)OCI_HTYPE_STMT, (size_t)0,
                                             (dvoid **)0)))
        return(rval);

      /* prepare the statement */
      if (qxiqtce(ctx, errhp, OCIStmtPrepare(stmthp, errhp, (text *)updstmt,
                                             (ub4)strlen(updstmt), OCI_NTV_SYNTAX,
                                             OCI_DEFAULT)))
        return(rval);

      /* Set up bind for newval */
      if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)1,
                                           (dvoid *)newval,
                                           (sb4)(strlen(newval)+1),
                                           (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                           (ub2 *)0, (ub4)0, (ub4 *)0,
                                           (ub4)OCI_DEFAULT)))
        return(rval);

      /* Set up bind for rid */
      if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)2,
                                           (dvoid *)rid,
                                           (sb4)(strlen(rid)+1),
                                           (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                           (ub2 *)0, (ub4)0, (ub4 *)0,
                                           (ub4)OCI_DEFAULT)))
        return(rval);

      /* Set up bind for oldval */
      if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)3,
                                           (dvoid *)oldval,
                                           (sb4)(strlen(oldval)+1),
                                           (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                           (ub2 *)0, (ub4)0, (ub4 *)0,
                                           (ub4)OCI_DEFAULT)))
        return(rval);

      /* Execute statement */
      if (qxiqtce(ctx, errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1,
                                             (ub4)0, (OCISnapshot *)NULL,
                                             (OCISnapshot *)NULL,
                                             (ub4)OCI_DEFAULT)))
        return(rval);

      return(rval);
    }

### Implementation of the ODCIIndexStart Routine

The start routine performs the setup for an `sbtree` index scan. The query information in terms of the operator predicate, its arguments, and the bounds on return values are passed in as parameters to this function. The scan context that is shared among the index scan routines is an instance of the type `psbtree_im`. We have defined a C struct (`qxiqtim`) as a mapping for the object type. In addition, there is a C struct (`qxiqtin`) for the corresponding null object. Note that the C structs for the object type and its null object can be generated from the Object Type Translator (OTT).

    /* The index implementation type is an object type with a single RAW attribute
     * which will be used to store the context key value.
     * C mapping of the implementation type :
     */
    struct qxiqtim
    {
       OCIRaw *sctx_qxiqtim;
    };
    typedef struct qxiqtim qxiqtim;

    struct qxiqtin
    {
      short atomic_qxiqtin;
      short scind_qxiqtin;
    };
    typedef struct qxiqtin qxiqtin;

This function sets up a cursor that scans the index table. The scan retrieves the stored rowids for the rows in the index table that satisfy the specified predicate. The predicate for the index table is generated based on the operator predicate information that is passed in as parameters. For example, if the operator predicate is of the form:

    eq(col, 'joe') = 1

the predicate on the index table is set up to be

    f1 = 'joe'

There are a set of OCI handles that need to be cached away and retrieved on the next fetch call. A C struct `qxiqtcx` is defined to hold all the necessary scan state. This structure is allocated out of `OCI_DURATION_STATEMENT` memory to ensure that it persists till the end of `fetch`. After populating the structure with the required info, a pointer to the structure is saved in OCI context. The context is identified by a 4-byte key that is generated by calling an OCI routine. The 4-byte key is stashed away in the scan context - `exiting`. This object is returned back to the Oracle server and is passed in as a parameter to the next fetch call.

    /* The index scan context - should be stored in "statement" duration memory
     * and used by start, fetch and close routines.
     */
    struct qxiqtcx
    {
      OCIStmt *stmthp;
      OCIDefine *defnp;
      OCIBind *bndp;
      char ridp[19];
    };

    typedef struct qxiqtcx qxiqtcx;

    OCINumber *qxiqtbs(ctx, sctx, sctx_ind, ix, ix_ind, pr, pr_ind, qy, qy_ind,
                       strt, strt_ind, stop, stop_ind, cmpval, cmpval_ind,
                       env, env_ind)
    OCIExtProcContext *ctx;
    qxiqtim           *sctx;
    qxiqtin           *sctx_ind;
    ODCIIndexInfo     *ix;
    ODCIIndexInfo_ind *ix_ind;
    ODCIPredInfo      *pr;
    dvoid             *pr_ind;
    ODCIQueryInfo     *qy;
    dvoid             *qy_ind;
    OCINumber         *strt;
    short             strt_ind;
    OCINumber         *stop;
    short             stop_ind;
    char              *cmpval;
    short             cmpval_ind;
    ODCIEnv           *env;
    dvoid             *env_ind;
    {
      sword status;
      OCIEnv *envhp;                                              /* env. handle */
      OCISvcCtx *svchp;                                        /* service handle */
      OCIError *errhp;                                           /* error handle */
      OCISession *usrhp;                                          /* user handle */
      qxiqtcx *icx;                         /* state to be saved for later calls */

      int strtval;                                                /* start bound */
      int stopval;                                                 /* stop bound */

      int errnum = 29400;                     /* choose some oracle error number */
      char errmsg[512];                                  /* error message buffer */
      size_t errmsglen;                               /* Length of error message */

      char relop[3];                     /* relational operator used in sql stmt */
      char selstmt[2000];                                /* sql select statement */

      int retval = (int)ODCI_SUCCESS;               /* return from this function */
      OCINumber *rval = (OCINumber *)0;
      ub4 key;                                        /* key value set in "sctx" */

      /* Get oci handles */
      if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
        return(rval);

      /* set up return code */
      rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
      if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                               sizeof(retval),
                                               OCI_NUMBER_SIGNED, rval)))
        return(rval);

      /* get the user handle */
      if (qxiqtce(ctx, errhp, OCIAttrGet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
                                         (dvoid *)&usrhp, (ub4 *)0,
                                         (ub4)OCI_ATTR_SESSION,
                                         errhp)))
        return(rval);

      /**********************************************/
      /* Allocate memory to hold index scan context */
      /**********************************************/
      if (qxiqtce(ctx, errhp, OCIMemoryAlloc((dvoid *)usrhp, errhp,
                                             (dvoid **)&icx,
                                             OCI_DURATION_STATEMENT,
                                             (ub4)(sizeof(qxiqtcx)),
                                             OCI_MEMORY_CLEARED)))
        return(rval);

      icx->stmthp = (OCIStmt *)0;
      icx->defnp = (OCIDefine *)0;
      icx->bndp = (OCIBind *)0;

      /***********************************/
      /* Check that the bounds are valid */
      /***********************************/
      /* convert from oci numbers to native numbers */
      if (qxiqtce(ctx, errhp, OCINumberToInt(errhp, strt,
                                             sizeof(strtval), OCI_NUMBER_SIGNED,
                                             (dvoid *)&strtval)))
        return(rval);
      if (qxiqtce(ctx, errhp, OCINumberToInt(errhp, stop,
                                            sizeof(stopval),
                                            OCI_NUMBER_SIGNED, (dvoid *)&stopval)))
        return(rval);

      /* verify that strtval/stopval are both either 0 or 1 */
      if (!(((strtval == 0) && (stopval == 0)) ||
            ((strtval == 1) && (stopval == 1))))
        {
          strcpy(errmsg, "Incorrect predicate for sbtree operator");
          errmsglen = (size_t)strlen(errmsg);
          if (OCIExtProcRaiseExcpWithMsg(ctx, errnum, (text *)errmsg, errmsglen)
              != OCIEXTPROC_SUCCESS)
            /* Use cartridge error services here */;
          return(rval);
        }

      /*********************************************/
      /* Generate the SQL statement to be executed */
      /*********************************************/
      if (memcmp((dvoid *)OCIStringPtr(envhp, pr->ObjectName), "EQ", 2)
          == 0)
        if (strtval == 1)
          strcpy(relop, "=");
        else
          strcpy(relop, "!=");
      else if (memcmp((dvoid *)OCIStringPtr(envhp, pr->ObjectName), "LT",
                      2) == 0)
        if (strtval == 1)
          strcpy(relop, "<");
        else
          strcpy(relop, ">=");
      else
        if (strtval == 1)
          strcpy(relop, ">");
        else
          strcpy(relop, "<=");

      if (ix_ind->IndexPartition == OCI_IND_NULL)
      {
        sprintf(selstmt, "select f2 from %s.%s_sbtree where f1 %s :val",
                      OCIStringPtr(envhp, ix->IndexSchema),
                      OCIStringPtr(envhp, ix->IndexName), relop);
      }
      else
      {
        sprintf(selstmt, "select f2 from %s.%s_%s_sbtree where f1 %s :val",
                      OCIStringPtr(envhp, ix->IndexSchema),
                      OCIStringPtr(envhp, ix->IndexName),
                      OCIStringPtr(envhp, ix->IndexPartition), relop);
      }

      /***********************************/
      /* Parse, bind, define and execute */
      /***********************************/
      /* allocate stmt handle */
      if (qxiqtce(ctx, errhp,
                  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&(icx->stmthp),
                                 (ub4)OCI_HTYPE_STMT, (size_t)0,
                                 (dvoid **)0)))
        return(rval);
      /* prepare the statement */
      if (qxiqtce(ctx, errhp, OCIStmtPrepare(icx->stmthp, errhp, (text *)selstmt,
                                             (ub4)strlen(selstmt), OCI_NTV_SYNTAX,
                                             OCI_DEFAULT)))
        return(rval);

      /* Set up bind */
      if (qxiqtce(ctx, errhp,
                  OCIBindByPos(icx->stmthp, &(icx->bndp), errhp, (ub4)1,
                               (dvoid *)cmpval,
                               (sb4)(strlen(cmpval)+1),
                               (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                               (ub2 *)0, (ub4)0, (ub4 *)0,
                               (ub4)OCI_DEFAULT)))
        return(rval);

      /* Set up define */
      if (qxiqtce(ctx, errhp, OCIDefineByPos(icx->stmthp, &(icx->defnp), errhp,
                                             (ub4)1, (dvoid *)(icx->ridp),
                                             (sb4) sizeof(icx->ridp),
                                             (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                             (ub2 *)0, (ub4)OCI_DEFAULT)))
        return(rval);

      /* execute */
      if (qxiqtce(ctx, errhp, OCIStmtExecute(svchp, icx->stmthp, errhp, (ub4)0,
                                             (ub4)0, (OCISnapshot *)NULL,
                                             (OCISnapshot *)NULL,
                                             (ub4)OCI_DEFAULT)))
        return(rval);

      /************************************/
      /* Set index context to be returned */
      /************************************/
      /* generate a key */
      if (qxiqtce(ctx, errhp, OCIContextGenerateKey((dvoid *)usrhp, errhp, &key)))
        return(rval);
      /* set the memory address of the struct to be saved in the context */
      if (qxiqtce(ctx, errhp, OCIContextSetValue((dvoid *)usrhp, errhp,
                                                 OCI_DURATION_STATEMENT,
                                                 (ub1 *)&key, (ub1)sizeof(key),
                                                 (dvoid *)icx)))
        return(rval);
      /* set the key as the member of "sctx" */
      if (qxiqtce(ctx, errhp, OCIRawAssignBytes(envhp, errhp, (ub1 *)&key,
                                                (ub4)sizeof(key),
                                                &(sctx->sctx_qxiqtim))))
        return(rval);

      sctx_ind->atomic_qxiqtin = OCI_IND_NOTNULL;
      sctx_ind->scind_qxiqtin = OCI_IND_NOTNULL;

      return(rval);
    }

### Implementation of the ODCIIndexFetch Routine

The scan context set up by the start routine is passed in as a parameter to the fetch routine. This function first retrieves the 4-byte key from the scan context. The C mapping for the scan context is `qxiqtim`. Next, the OCI context is looked up based on the key. This gives the memory address of the structure that holds the OCI handles - the `qxiqtcx` structure.

This function returns the next batch of rowids that satisfy the operator predicate. It uses the value of the `nrows` parameter as the size of the batch. It repeatedly fetches rowids from the open cursor and populates the `rowid` list with them. When the batch is full or when there are no more rowids left, the function returns them back to the Oracle server.

    OCINumber *qxiqtbf(ctx, self, self_ind, nrows, nrows_ind, rids, rids_ind,
                       env, env_ind)
    OCIExtProcContext *ctx;
    qxiqtim           *self;
    qxiqtin           *self_ind;
    OCINumber         *nrows;
    short             nrows_ind;
    OCIArray          **rids;
    short             *rids_ind;
    ODCIEnv           *env;
    dvoid             *env_ind;
    {
      sword status;
      OCIEnv *envhp;
      OCISvcCtx *svchp;
      OCIError *errhp;
      OCISession *usrhp;                                          /* user handle */
      qxiqtcx *icx;

      int idx = 1;
      int nrowsval;

      OCIArray *ridarrp = *rids;                             /* rowid collection */
      OCIString *ridstr = (OCIString *)0;

      int done = 0;
      int retval = (int)ODCI_SUCCESS;
      OCINumber *rval = (OCINumber *)0;

      ub1 *key;                                       /* key to retrieve context */
      ub4 keylen;                                               /* length of key */

      /*******************/
      /* Get OCI handles */
      /*******************/
      if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
          return(rval);

      /* set up return code */
      rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
      if (qxiqtce(ctx, errhp,
                  OCINumberFromInt(errhp, (dvoid *)&retval, sizeof(retval),
                                               OCI_NUMBER_SIGNED, rval)))
        return(rval);

      /* get the user handle */
      if (qxiqtce(ctx, errhp, OCIAttrGet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
                                         (dvoid *)&usrhp, (ub4 *)0,
                                         (ub4)OCI_ATTR_SESSION, errhp)))
        return(rval);

      /********************************/
      /* Retrieve context from key    */
      /********************************/
      key = OCIRawPtr(envhp, self->sctx_qxiqtim);
      keylen = OCIRawSize(envhp, self->sctx_qxiqtim);

      if (qxiqtce(ctx, errhp, OCIContextGetValue((dvoid *)usrhp, errhp,
                                                 key, (ub1)keylen,
                                                 (dvoid **)&(icx))))
        return(rval);

      /* get value of nrows */
      if (qxiqtce(ctx, errhp, OCINumberToInt(errhp, nrows, sizeof(nrowsval),
                                           OCI_NUMBER_SIGNED, (dvoid *)&nrowsval)))
        return(rval);

      /****************/
      /* Fetch rowids */
      /****************/
      while (!done)
      {
        if (idx > nrowsval)
          done = 1;
        else
        {
          status = OCIStmtFetch(icx->stmthp, errhp, (ub4)1, (ub2) 0,
                                (ub4)OCI_DEFAULT);
          if (status == OCI_NO_DATA)
          {
            short col_ind = OCI_IND_NULL;
            /* have to create dummy oci string */
            OCIStringAssignText(envhp, errhp, (text *)"dummy",
                                (ub2)5, &ridstr);
            /* append null element to collection */
            if (qxiqtce(ctx, errhp, OCICollAppend(envhp, errhp,(dvoid *)ridstr,
                                                  (dvoid *)&col_ind,
                                                  (OCIColl *)ridarrp)))
              return(rval);
            done = 1;
          }
          else if (status == OCI_SUCCESS)
          {
            OCIStringAssignText(envhp, errhp, (text *)icx->ridp,
                                (ub2)18, (OCIString **)&ridstr);
            /* append rowid to collection */
            if (qxiqtce(ctx, errhp, OCICollAppend(envhp, errhp, (dvoid *)ridstr,
                                                  (dvoid *)0, (OCIColl *)ridarrp)))
              return(rval);
            idx++;
          }
          else if (qxiqtce(ctx, errhp, status))
            return(rval);
        }
      }

      /* free ridstr finally */
      if (ridstr &&
          (qxiqtce(ctx, errhp, OCIStringResize(envhp, errhp, (ub4)0,
                                               &ridstr))))
        return(rval);

      *rids_ind = OCI_IND_NOTNULL;

      return(rval);
    }

### Implementation of the ODCIIndexClose Routine

The scan context set up by the start routine is passed in as a parameter to the close routine. This function first retrieves the 4-byte key from the scan context. The C mapping for the scan context is `qxiqtim`. Next, the OCI context is looked up based on the key. This gives the memory address of the structure that holds the OCI handles - the `qxiqtcx` structure.

The function closes and frees all the OCI handles. It also frees the memory that was allocated in the start routine.

    OCINumber *qxiqtbc(ctx, self, self_ind, env, env_ind)
    OCIExtProcContext *ctx;
    qxiqtim           *self;
    qxiqtin           *self_ind;
    ODCIEnv           *env;
    dvoid             *env_ind;
    {
      sword status;
      OCIEnv *envhp;
      OCISvcCtx *svchp;
      OCIError *errhp;
      OCISession *usrhp;                                          /* user handle */

      qxiqtcx *icx;

      int retval = (int) ODCI_SUCCESS;
      OCINumber *rval = (OCINumber *)0;

      ub1 *key;                                       /* key to retrieve context */
      ub4 keylen;                                               /* length of key */

      if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
          return(rval);

      /* set up return code */
      rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
      if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                               sizeof(retval),
                                               OCI_NUMBER_SIGNED, rval)))
        return(rval);

      /* get the user handle */
      if (qxiqtce(ctx, errhp, OCIAttrGet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
                                         (dvoid *)&usrhp, (ub4 *)0,
                                         (ub4)OCI_ATTR_SESSION, errhp)))
        return(rval);

      /********************************/
      /* Retrieve context using key   */
      /********************************/
      key = OCIRawPtr(envhp, self->sctx_qxiqtim);
      keylen = OCIRawSize(envhp, self->sctx_qxiqtim);

      if (qxiqtce(ctx, errhp, OCIContextGetValue((dvoid *)usrhp, errhp,
                                                 key, (ub1)keylen,
                                                 (dvoid **)&(icx))))
        return(rval);

      /* Free handles and memory */
      if (qxiqtce(ctx, errhp, OCIHandleFree((dvoid *)icx->stmthp,
                                            (ub4)OCI_HTYPE_STMT)))
        return(rval);

      if (qxiqtce(ctx, errhp, OCIMemoryFree((dvoid *)usrhp, errhp, (dvoid *)icx)))
        return(rval);

      return(rval);
    }

## Implementing the Indextype

Create the indextype object and specify the list of operators that it supports. In addition, specify the name of the implementation type that implements the ODCIIndex interface routines.

    CREATE INDEXTYPE psbtree
    FOR
    eq(VARCHAR2, VARCHAR2),
    lt(VARCHAR2, VARCHAR2),
    gt(VARCHAR2, VARCHAR2)
    USING psbtree_im
    WITH LOCAL RANGE PARTITION

## Usage Examples

One typical usage scenario is described in the following example. Create a range partitioned table and populate it.

    CREATE TABLE t1 (f1 NUMBER, f2 VARCHAR2(200))
    PARTITION BY RANGE(f1)
    (
      PARTITION p1 VALUES LESS THAN (101),
      PARTITION p2 VALUES LESS THAN (201),
      PARTITION p3 VALUES LESS THAN (301),
      PARTITION p4 VALUES LESS THAN (401)
     );
    INSERT INTO t1 VALUES (10, 'aaaa');
    INSERT INTO t1 VALUES (200, 'bbbb');
    INSERT INTO t1 VALUES (100, 'cccc');
    INSERT INTO t1 VALUES (300, 'dddd');
    INSERT INTO t1 VALUES (400, 'eeee');
    COMMIT;

Create a `psbtree` index on column f2. The create index statement specifies the indextype to be used.

    CREATE INDEX it1 ON t1(f2) iINDEXTYPE IS psbtree LOCAL
    (PARTITION pe1 PARAMETERS('test1'), PARTITION pe2,
     PARTITION pe3, PARTITION pe4 PARAMETERS('test4'))
    PARAMETERS('test');

Execute a query that uses one of the `sbtree` operators. •

    SELECT * FROMM t1 WHERE eq(f2, 'dddd') = 1 AND f1>101 ;

### Explain Plan Output

    OPERATION            OPTIONS                PARTITION_START       PARTITION_STOP
    --------------------------------------------------------------------------------
    SELECT STATEMENT
    PARTITION RANGE      ITERATOR               2                     4
    TABLE ACCESS         BY LOCAL INDEX ROWID   2                     4
    DOMAIN INDEX